﻿using PersistentObject.Common.Criteria;
using PersistentObject.Common.Schema;
using PersistentObject.Common.Sorts;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PersistentObject.Common
{
    public class BaseSqlCreator
    {
        public Table MainTable { get; protected set; }

        public BaseDialect Dialect { get; protected set; }

        public List<Column> ForeignColumnList { get; protected set; }

        public BaseSqlCreator(Table mainTable, BaseDialect dialect)
        {
            MainTable = mainTable;
            Dialect = dialect;
            RetrieveForeignColumnList();
        }

        public BaseSqlCreator(Type type, BaseDialect dialect)
        {
            MainTable = Dialect.SchemaHelper.CreateTable(type);
            Dialect = dialect;
            RetrieveForeignColumnList();
        }

        public virtual string CreateCountSql(BaseCriteria criteria)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select count(1) from").AppendLine();
            var fromTableLines = GetFromClauseLines();
            sb.Append(fromTableLines).AppendLine();

            if (criteria != null)
            {
                BaseCriteriaCreator criteriaCreator = Dialect.GetCriteriaCreator(MainTable);
                var criteriaClause = criteriaCreator.GetCriteriaClause(criteria);
                sb.Append("where ").AppendLine();
                sb.Append(criteriaClause).AppendLine();
            }

            var sql = sb.ToString();
            return sql;
        }

        #region select sql
        public virtual string CreateSelectSql(BaseCriteria criteria, OrderBy[] orderBys)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select").AppendLine();
            var selectColumnLines = GetSelectClauseLines();
            sb.Append(selectColumnLines);
            sb.Append("from").AppendLine();
            var fromTableLines = GetFromClauseLines();
            sb.Append(fromTableLines).AppendLine();

            if (criteria != null)
            {
                BaseCriteriaCreator criteriaCreator = Dialect.GetCriteriaCreator(MainTable);
                var criteriaClause = criteriaCreator.GetCriteriaClause(criteria);
                sb.Append("where ").AppendLine();
                sb.Append(criteriaClause).AppendLine();
            }

            if(orderBys != null && orderBys.Count() > 0)
            {
                BaseOrderByCreator orderByCreator = new BaseOrderByCreator(MainTable, this.Dialect);
                sb.Append("order by ").AppendLine();
                for(int i=0;i<orderBys.Count();i++)
                {
                    OrderBy orderBy = orderBys[i];
                    var orderByClause = orderByCreator.GetOrderByClause(orderBy);
                    if (i > 0)
                    {
                        sb.Append(",");
                    }
                    sb.Append(orderByClause);
                }
                sb.AppendLine();
            }
            //sb.Append(";");

            var sql = sb.ToString();
            return sql;
        }

        /// <summary>
        /// select objects from database
        /// </summary>
        /// <param name="criteria"></param>
        /// <param name="orderBys"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex">0-base</param>
        /// <returns></returns>
        public virtual string CreateSelectSql(BaseCriteria criteria, OrderBy[] orderBys, int pageSize, int pageIndex)
        {
            //StringBuilder sb = new StringBuilder();
            //sb.Append("select").AppendLine();
            //var selectColumnLines = GetSelectClauseLines();
            //sb.Append(selectColumnLines);
            //sb.Append("from").AppendLine();
            //var fromTableLines = GetFromClauseLines();
            //sb.Append(fromTableLines);

            //if (criteria != null)
            //{
            //    BaseCriteriaCreator criteriaCreator = new BaseCriteriaCreator(MainTable, this.Dialect);
            //    var criteriaClause = criteriaCreator.GetCriteriaClause(criteria);
            //    sb.Append("where ").AppendLine();
            //    sb.Append(criteriaClause).AppendLine();
            //}

            //if (orderBys != null && orderBys.Count() > 0)
            //{
            //    BaseOrderByCreator orderByCreator = new BaseOrderByCreator(MainTable, this.Dialect);
            //    sb.Append("order by ").AppendLine();
            //    for (int i = 0; i < orderBys.Count(); i++)
            //    {
            //        OrderBy orderBy = orderBys[i];
            //        var orderByClause = orderByCreator.GetOrderByClause(orderBy);
            //        if (i > 0)
            //        {
            //            sb.Append(",");
            //        }
            //        sb.Append(orderByClause);
            //    }
            //    sb.AppendLine();
            //}

            //var pagingClause = string.Format("offset ({0} * {1}) row fetch next {0} rows only", pageSize, pageIndex);
            //sb.Append(pagingClause).AppendLine();

            ////sb.Append(";");
            //var sql = sb.ToString();
            //return sql;

            StringBuilder sb = new StringBuilder();
            sb.Append("select * from (").AppendLine();

            sb.Append("select").AppendLine();
            var selectColumnLines = GetSelectClauseLines();
            sb.Append(selectColumnLines);
            sb.Append(",row_number() over (order by ");

            if (orderBys != null && orderBys.Count() > 0)
            {
                BaseOrderByCreator orderByCreator = new BaseOrderByCreator(MainTable, this.Dialect);
                for (int i = 0; i < orderBys.Count(); i++)
                {
                    OrderBy orderBy = orderBys[i];
                    var orderByClause = orderByCreator.GetOrderByClause(orderBy);
                    if (i > 0)
                    {
                        sb.Append(",");
                    }
                    sb.Append(orderByClause);
                }
                sb.AppendLine();
            }
            else
            {
                sb.Append("(select 1)").AppendLine();
            }

            sb.Append(") as [__PAGING__]").AppendLine();
            sb.Append("from").AppendLine();
            var fromTableLines = GetFromClauseLines();
            sb.Append(fromTableLines);

            if (criteria != null)
            {
                BaseCriteriaCreator criteriaCreator = new BaseCriteriaCreator(MainTable, this.Dialect);
                var criteriaClause = criteriaCreator.GetCriteriaClause(criteria);
                sb.Append("where ").AppendLine();
                sb.Append(criteriaClause).AppendLine();
            }
            sb.Append(")X").AppendLine();

            var pagingClause = string.Format("where [__PAGING__] between {0} and {1}", pageIndex * pageSize + 1, (pageIndex + 1) * pageSize);
            sb.Append(pagingClause).AppendLine();

            //sb.Append(";");
            var sql = sb.ToString();
            return sql;
        }

        public virtual string CreateInnerExistsSql(BaseCriteria criteria)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select 1").AppendLine();
            sb.Append("from").AppendLine();
            var fromTableLines = GetFromClauseLines();
            sb.Append(fromTableLines).AppendLine();

            if (criteria != null)
            {
                BaseCriteriaCreator criteriaCreator = new BaseCriteriaCreator(MainTable, this.Dialect);
                var criteriaClause = criteriaCreator.GetCriteriaClause(criteria);
                sb.Append("where ").AppendLine();
                sb.Append(criteriaClause).AppendLine();
            }

            var sql = sb.ToString();
            return sql;
        }

        protected virtual void RetrieveForeignColumnList()
        {
            ForeignColumnList = new List<Column>();
            Queue<Column> foreignColumnQueue = new Queue<Column>();
            foreach (var column in MainTable.ColumnList.FindAll((item) => item.IsForeignKeyColumn))
            {
                foreignColumnQueue.Enqueue(column);
            }
            while (foreignColumnQueue.Count() > 0)
            {
                Column curColumn = foreignColumnQueue.Dequeue();
                ForeignColumnList.Add(curColumn);
                Table foreignTable = curColumn.ForeignTable;
                foreach (var column in foreignTable.ColumnList.FindAll((item) => item.IsForeignKeyColumn))
                {
                    foreignColumnQueue.Enqueue(column);
                }
            }
        }

        protected virtual string GetSelectClauseLines()
        {
            StringBuilder sb = new StringBuilder();

            var selectMainTableLines = GetSingleSelectClauseLinesBySingleTable(MainTable, ForeignColumnList.Count > 0);
            foreach (var line in selectMainTableLines)
            {
                sb.Append(line).AppendLine();
            }

            for (int t = 0; t < ForeignColumnList.Count; t++)
            {
                var foreignColumn = ForeignColumnList[t];
                var foreignTable = foreignColumn.ForeignTable;
                var selectForeignTableLines = GetSingleSelectClauseLinesBySingleTable(foreignTable, t < ForeignColumnList.Count - 1);
                foreach (var line in selectForeignTableLines)
                {
                    sb.Append(line).AppendLine();
                }
            }
            return sb.ToString();
        }

        protected virtual IEnumerable<string> GetSingleSelectClauseLinesBySingleTable(Table table, bool hasExtraForeignTable)
        {
            List<string> lines = new List<string>();
            var keyColumnLine = GetSingleSelectClauseLine(table.KeyColumn);
            bool isEndLine = !hasExtraForeignTable && table.ColumnList.Count == 0;
            if (!isEndLine)
            {
                keyColumnLine += ",";
            }
            lines.Add(keyColumnLine);

            for (int c = 0; c < table.ColumnList.Count; c++)
            {
                var column = table.ColumnList[c];
                var columnLine = GetSingleSelectClauseLine(column);
                isEndLine = !hasExtraForeignTable && c == table.ColumnList.Count - 1;
                if (!isEndLine)
                {
                    columnLine += ",";
                }
                lines.Add(columnLine);
            }
            return lines;
        }

        protected virtual string GetSingleSelectClauseLine(Column column)
        {
            var columnWithTableAliasInQuote = Dialect.NamingPattern.GetColumnNameWithTableAliasInQuote(column);
            string columnAliasInQuote = Dialect.NamingPattern.GetColumnAliasInQuote(column);
            string line = string.Format("{0} as {1}", columnWithTableAliasInQuote, columnAliasInQuote);
            return line;
        }

        protected virtual string GetFromClauseLines()
        {
            StringBuilder sb = new StringBuilder();

            var fromMainTableLine = GetMainFromClauseLine();
            sb.Append(fromMainTableLine).AppendLine();

            for (int t = 0; t < ForeignColumnList.Count; t++)
            {
                var foreignColumn = ForeignColumnList[t];
                var foreignTable = foreignColumn.ForeignTable;
                var fromForeignTableLine = GetForeignFromClauseLine(foreignColumn);
                sb.Append(fromForeignTableLine).AppendLine();
            }
            return sb.ToString();
        }

        protected virtual string GetMainFromClauseLine()
        {
            var tableNameInQuote = Dialect.NamingPattern.GetTableNameInQuote(MainTable);
            var tableAliasInQuote = Dialect.NamingPattern.GetTableAliasInQuote(MainTable);
            string fromMainTableLine = string.Format("{0} {1}", tableNameInQuote, tableAliasInQuote);
            return fromMainTableLine;
        }

        protected virtual string GetForeignFromClauseLine(Column foreignColumn)
        {
            var mainTable = foreignColumn.Table;
            //var mainTableAlias = Dialect.NamingPattern.GetTableAlias(mainTable);
            var foreignTable = foreignColumn.ForeignTable;
            //string foreignTableAlias = Dialect.NamingPattern.GetTableAlias(foreignTable);

            var foreignTableNameInQuote = Dialect.NamingPattern.GetTableNameInQuote(foreignTable);
            var foreignTableAliasInQuote = Dialect.NamingPattern.GetTableAliasInQuote(foreignTable);
            var foreignKeyColumnWithTableAliasInQuote = Dialect.NamingPattern.GetColumnNameWithTableAliasInQuote(foreignTable.KeyColumn);
            var foreignColumnWithTableAliasInQuote = Dialect.NamingPattern.GetColumnNameWithTableAliasInQuote(foreignColumn);

            //string fromForeignTableLine = string.Format("left join [{0}] [{1}] on [{1}].[{2}] = [{3}].[{4}]",
            //    foreignTable.TableName, foreignTableAlias,
            //    foreignTable.KeyColumn.ColumnName,
            //    mainTableAlias, foreignColumn.ColumnName);
            string clause = string.Format("left join {0} {1} on {2} = {3}",
                foreignTableNameInQuote, foreignTableAliasInQuote,
                foreignKeyColumnWithTableAliasInQuote,
                foreignColumnWithTableAliasInQuote);
            return clause;
        }

        #endregion

        #region save sql
        public virtual string CreateInsertSql()
        {
            var keyColumnParameterName = Dialect.NamingPattern.GetSaveParameterName(MainTable.KeyColumn);
            var keyColumnAlias = Dialect.NamingPattern.GetColumnAlias(MainTable.KeyColumn);
            var keyColumnAliasInQuote = Dialect.NamingPattern.GetColumnAliasInQuote(MainTable.KeyColumn);

            StringBuilder sb = new StringBuilder();
            var mainTableNameInQuote = Dialect.NamingPattern.GetTableNameInQuote(MainTable);
            sb.Append(string.Format("insert into {0}", mainTableNameInQuote));
            sb.Append("(");
            if (!MainTable.KeyColumn.IsAutoGenerated)
            {
                var keyColumnNameInQuote = Dialect.NamingPattern.GetColumnNameInQuote(MainTable.KeyColumn);
                sb.Append(string.Format("{0}", keyColumnNameInQuote));
                if (MainTable.ColumnList.Count() > 0)
                {
                    sb.Append(",");
                }
            }
            for (int i = 0; i < MainTable.ColumnList.Count(); i++)
            {
                Column column = MainTable.ColumnList[i];
                var columnNameInQuote = Dialect.NamingPattern.GetColumnNameInQuote(column);
                if (i != 0)
                {
                    sb.Append(",");
                }
                sb.Append(string.Format("{0}", columnNameInQuote));
            }
            sb.Append(")").AppendLine();
            sb.Append("values(");
            if (!MainTable.KeyColumn.IsAutoGenerated)
            {
                sb.Append(keyColumnParameterName).Append(",");
            }
            for (int i = 0; i < MainTable.ColumnList.Count(); i++)
            {
                Column column = MainTable.ColumnList[i];
                var columnParameterName = Dialect.NamingPattern.GetSaveParameterName(column);
                if (i != 0)
                {
                    sb.Append(",");
                }
                sb.Append(columnParameterName);
            }
            sb.Append(");").AppendLine();
            if (MainTable.KeyColumn.IsAutoGenerated)
            {
                sb.Append(string.Format("select {0} = cast(@@identity as int);", keyColumnParameterName));
            }
            else
            {
                //sb.Append(string.Format("set {0} = {0};", keyColumnParameterName));
            }
            sb.AppendLine();
            var sql = sb.ToString();
            return sql;
        }

        public virtual string CreateUpdateSql()
        {
            var keyColumnParameterName = Dialect.NamingPattern.GetSaveParameterName(MainTable.KeyColumn);
            var keyColumnAlias = Dialect.NamingPattern.GetColumnAlias(MainTable.KeyColumn);
            var tableNameInQuote = Dialect.NamingPattern.GetTableNameInQuote(MainTable);

            StringBuilder sb = new StringBuilder();
            sb.Append(string.Format("update {0} set", tableNameInQuote)).AppendLine();
            for (int i = 0; i < MainTable.ColumnList.Count(); i++)
            {
                if (i != 0)
                {
                    sb.Append(",").AppendLine();
                }
                Column column = MainTable.ColumnList[i];
                var columnParameterName = Dialect.NamingPattern.GetSaveParameterName(column);
                var columnNameInQuote = Dialect.NamingPattern.GetColumnNameInQuote(column);
                var clause = string.Format("{0} = {1}", columnNameInQuote, columnParameterName);
                sb.Append(clause);
            }
            sb.AppendLine();
            var keyColumnNameInQuote = Dialect.NamingPattern.GetColumnNameInQuote(MainTable.KeyColumn);
            sb.Append(string.Format("where {0} = {1};", keyColumnNameInQuote, keyColumnParameterName));
            sb.AppendLine();
            var keyColumnAliasInQuote = Dialect.NamingPattern.GetColumnAliasInQuote(MainTable.KeyColumn);
            sb.Append(string.Format("select {2} = {0} from {1} where {0} = {2};", 
                keyColumnNameInQuote, tableNameInQuote,
                keyColumnParameterName));
            var sql = sb.ToString();
            return sql;
        }

        public virtual string CreateIsExistingSql()
        {
            var keyColumnParameterName = Dialect.NamingPattern.GetSaveParameterName(MainTable.KeyColumn);
            var tableNameInQuote = Dialect.NamingPattern.GetTableNameInQuote(MainTable);
            var keyColumnNameInQuote = Dialect.NamingPattern.GetColumnNameInQuote(MainTable.KeyColumn);

            StringBuilder sb = new StringBuilder();
            sb.Append("select count(1)").AppendLine();
            sb.Append(string.Format("from {0}", tableNameInQuote)).AppendLine();
            sb.Append(string.Format("where {0} = {1}", keyColumnNameInQuote, keyColumnParameterName)).AppendLine();
            var sql = sb.ToString();
            return sql;
        }
        #endregion

        #region delete sql
        public virtual string CreateDeleteSql(BaseCriteria criteria)
        {
            if (criteria == null)
            {
                return CreateDeleteSql();
            }

            var tableNameInQuote = Dialect.NamingPattern.GetTableNameInQuote(MainTable);
            var tableAliasInQuote = Dialect.NamingPattern.GetTableAliasInQuote(MainTable);

            StringBuilder sb = new StringBuilder();
            sb.Append(string.Format("delete {0}", tableAliasInQuote)).AppendLine();
            sb.Append("from").AppendLine();
            var fromTableLines = GetFromClauseLines();
            sb.Append(fromTableLines);

            BaseCriteriaCreator criteriaCreator = new BaseCriteriaCreator(MainTable, this.Dialect);
            var criteriaClause = criteriaCreator.GetCriteriaClause(criteria);
            sb.Append("where ").AppendLine();
            sb.Append(criteriaClause).AppendLine();

            var sql = sb.ToString();
            return sql;
        }

        public virtual string CreateDeleteSql()
        {
            var keyColumnParameterName = Dialect.NamingPattern.GetSaveParameterName(MainTable.KeyColumn);
            var tableNameInQuote = Dialect.NamingPattern.GetTableNameInQuote(MainTable);
            var tableAliasInQuote = Dialect.NamingPattern.GetTableAliasInQuote(MainTable);
            var keyColumnNameInQuote = Dialect.NamingPattern.GetColumnNameWithTableAliasInQuote(MainTable.KeyColumn);

            StringBuilder sb = new StringBuilder();
            sb.Append(string.Format("delete {0}", tableAliasInQuote)).AppendLine();
            sb.Append("from").AppendLine();
            var fromTableLines = GetFromClauseLines();
            sb.Append(fromTableLines);
            sb.Append(string.Format("where {0} = {1}", keyColumnNameInQuote, keyColumnParameterName)).AppendLine();
            var sql = sb.ToString();
            return sql;
        }
        #endregion
    }
}
